#!/usr/bin/env python3 
# -*- coding: utf-8 -*-
# @Author  : Grit<dooms21day@163.com>
# @Company : 成都慧创智通信息技术有限公司
# @Brand   : 企慧蚁
# @Copyright: 2021-
from odoo import api, fields, models
from odoo.tools import float_is_zero, groupby
from odoo.addons import decimal_precision as dp


class StockTurnoverReport(models.Model):
    _name = "stock.turnover.report"
    _description = "库存周转率"
    _rec_name = "product_id"

    wizard_id = fields.Many2one("general.report.wizard", "报告查询", index=True, ondelete="CASCADE")
    product_id = fields.Many2one("product.product", "产品", required=True)
    product_uom_id = fields.Many2one("uom.uom", "单位", required=True)
    company_id = fields.Many2one("res.company", "公司", required=True)

    last_period_qty = fields.Float("上期数量", digits=dp.get_precision('Product Unit of Measure'))
    last_period_amount = fields.Float("上期金额", digits=(16, 2))
    current_period_qty = fields.Float("本期数量", digits=dp.get_precision('Product Unit of Measure'))
    current_period_amount = fields.Float("本期金额", digits=(16, 2))
    average_qty = fields.Float("平均数量", digits=dp.get_precision('Product Unit of Measure'))
    current_out_qty = fields.Float("本期发出数量", digits=dp.get_precision('Product Unit of Measure'))
    current_out_amount = fields.Float("本期发出金额", digits=(16, 2))
    current_turnover_rate = fields.Float("本期库存周转率(%)", digits=(16, 2))


    @api.model
    def compute_report_data(self, date_start, date_end, domain, wizard_id):
        """
        :return:
        """
        decimal_obj = self.env['decimal.precision']
        price_decimal = decimal_obj.precision_get('Product Price')
        quantity_decimal = decimal_obj.precision_get("Product Unit of Measure")

        sql_query = self.env['stock.move']._where_calc(domain)
        from_clause, where_clause, where_clause_params = sql_query.get_sql()

        select_str = """
            SELECT
                stock_move.product_id,
                stock_move.date,
                stock_move.product_qty,
                stock_move.company_id,
                stock_move.product_uom as product_uom_id,
                stock_move.price_unit,
            CASE

                WHEN (
                    sls.company_id IS NULL 
                    AND ( sld.USAGE = 'internal' OR ( sld.company_id = stock_move.company_id AND sld.USAGE = 'transit' ) ) 
                    ) THEN
                    'in' 
                    WHEN (
                        ( sls.USAGE = 'internal' OR ( sls.company_id = stock_move.company_id AND sls.USAGE = 'transit' ) ) 
                        AND sld.company_id IS NULL 
                        ) THEN
                        'out' 
                    END AS direct,
                CASE

                        WHEN (
                            sls.company_id IS NULL 
                            AND ( sld.USAGE = 'internal' OR ( sld.company_id = stock_move.company_id AND sld.USAGE = 'transit' ) ) 
                            ) THEN
                            stock_move.location_dest_id 
                            WHEN (
                                ( sls.USAGE = 'internal' OR ( sls.company_id = stock_move.company_id AND sls.USAGE = 'transit' ) ) 
                                AND sld.company_id IS NULL 
                                ) THEN
                                stock_move.location_id 
                            END AS location_id 
        """

        from_str = """
            FROM
                stock_move
                LEFT JOIN stock_location AS sls ON ( stock_move.location_id = sls.ID )
                LEFT JOIN stock_location AS sld ON ( stock_move.location_dest_id = sld.ID )        
        """

        where_str = """
                stock_move.STATE = 'done' 
                AND ((
                    sls.company_id IS NULL 
                    AND ( sld.USAGE = 'internal' OR ( sld.company_id = stock_move.company_id AND sld.USAGE = 'transit' ) ) 
                ) 
                OR (
                    ( sls.USAGE = 'internal' OR ( sls.company_id = stock_move.company_id AND sls.USAGE = 'transit' ) ) 
                AND sld.company_id IS NULL 
            )) 
        """
        if where_clause:
            where_str = "WHERE " + where_clause + " AND " + where_str
        else:
            where_str = "WHERE " + where_str

        sql_str = select_str + from_str + where_str

        self._cr.execute(sql_str, where_clause_params)
        res = self._cr.dictfetchall()

        # 处理后需要创建的数据
        def _move_keys(x):
            return x['company_id'], x['product_id'], x['product_uom_id']

        report_data = []
        for move_keys, moves in groupby(res, _move_keys):
            temp = {
                "product_id": move_keys[1],
                "company_id": move_keys[0],
                "product_uom_id": move_keys[2],
                "wizard_id": wizard_id,
            }
            last_period_qty = last_period_amount = current_out_qty = \
                current_out_amount = current_qty = current_amount = 0
            for move in moves:
                temp_qty = move['product_qty']
                temp_amount = move['product_qty'] * abs(move['price_unit'] or 0)
                # 时间使用左闭右开
                if move['direct'] == 'in':
                    if move['date'] < date_start:
                        last_period_qty += temp_qty
                        last_period_amount += temp_amount
                    elif move['date'] < date_end:
                        current_qty += temp_qty
                        current_amount += temp_amount
                elif move['direct'] == 'out':
                    if move['date'] < date_start:
                        last_period_qty -= temp_qty
                        last_period_amount -= temp_amount
                    elif move['date'] < date_end:
                        current_out_qty += temp_qty
                        current_out_amount += temp_amount
                        current_amount -= temp_amount
                        current_qty -= temp_qty
            current_period_qty = last_period_qty + current_qty
            average_qty = (last_period_qty + current_period_qty) / 2
            current_turnover_rate = 0 if float_is_zero(average_qty, quantity_decimal) \
                else (current_out_qty / average_qty)
            temp.update(
                last_period_qty=last_period_qty, last_period_amount=last_period_amount,
                current_period_qty=current_period_qty, current_out_qty=current_out_qty,
                current_out_amount=current_amount, current_turnover_rate=current_turnover_rate,
                average_qty=average_qty
            )
            report_data.append(temp)
        return self.create(report_data)